package com.richard.room.operator.api.helper;

import android.annotation.SuppressLint;
import android.text.TextUtils;
import android.util.ArrayMap;
import android.util.Log;

import androidx.annotation.NonNull;
import androidx.room.EntityDeleteOrUpdateAdapter;
import androidx.room.EntityInsertAdapter;
import androidx.room.OnConflictStrategy;
import androidx.room.RoomDatabase;
import androidx.room.util.DBUtil;
import androidx.room.util.SQLiteConnectionUtil;
import androidx.room.util.SQLiteStatementUtil;
import androidx.room.util.StringUtil;
import androidx.sqlite.SQLite;
import androidx.sqlite.SQLiteStatement;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.util.TypeUtils;
import com.richard.room.operator.api.Entity;
import com.richard.room.operator.api.Property;
import com.richard.room.operator.api.RoomOperator;
import com.richard.room.operator.api.statement.DeleteSQL;
import com.richard.room.operator.api.statement.SelectSQL;
import com.richard.room.operator.api.statement.UpdateSQL;
import com.richard.room.operator.api.statement.element.Condition;
import com.richard.room.operator.api.statement.element.Expression;
import com.richard.room.operator.api.statement.element.Join;
import com.richard.room.operator.api.type.FuncType;
import com.richard.room.operator.api.type.OrderByType;
import com.richard.room.operator.api.utils.Utils;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author: Richard
 * @createDate: 2023/8/10 17:13
 * @version: 1.0
 * @description: sqlite 数据表 CURD
 */
@SuppressWarnings("unchecked")
@SuppressLint("RestrictedApi")
public final class SQLiteHelper {

    private static final String TAG = "SQLiteHelper";

    private static final ArrayMap<String, EntityInsertAdapter<?>> insertionAdapterMap = new ArrayMap<>();
    private static final ArrayMap<String, EntityDeleteOrUpdateAdapter<?>> deleteAdapterMap = new ArrayMap<>();
    private static final ArrayMap<String, EntityDeleteOrUpdateAdapter<?>> updateAdapterMap = new ArrayMap<>();

    private SQLiteHelper() {
    }


    /**
     * 获取EntityInsertionAdapter
     *
     * @param entity             实体信息
     * @param onConflictStrategy insert 策略(详见OnConflictStrategy)
     */
    public static <T> EntityInsertAdapter<T> getEntityInsertAdapter(Entity<T> entity, int onConflictStrategy) {
        String key = String.format("%s_%s", entity.getEntityClass().getName(), onConflictStrategy);

        EntityInsertAdapter<?> value = insertionAdapterMap.get(key);
        if (value == null) {
            synchronized (SQLiteHelper.class) {
                value = SQLiteHelper.newEntityInsertionAdapter(entity, onConflictStrategy);
                insertionAdapterMap.put(key, value);
            }
        }
        return (EntityInsertAdapter<T>) value;
    }

    /**
     * 获取delete adapter
     *
     * @param entity 实体信息
     */
    public static <T> EntityDeleteOrUpdateAdapter<T> getEntityDeleteAdapter(Entity<T> entity) {
        EntityDeleteOrUpdateAdapter<?> value = deleteAdapterMap.get(entity.getEntityClass().getName());
        if (value == null) {
            synchronized (SQLiteHelper.class) {
                value = SQLiteHelper.newEntityDeleteAdapter(entity);
                deleteAdapterMap.put(entity.getEntityClass().getName(), value);
            }
        }
        return (EntityDeleteOrUpdateAdapter<T>) value;
    }

    /**
     * 获取update adapter
     *
     * @param entity             实体信息
     * @param onConflictStrategy insert 策略(详见OnConflictStrategy)
     */
    public static <T> EntityDeleteOrUpdateAdapter<T> getEntityUpdateAdapter(Entity<T> entity, int onConflictStrategy) {
        String key = String.format("%s_%s", entity.getEntityClass().getName(), onConflictStrategy);

        EntityDeleteOrUpdateAdapter<?> value = updateAdapterMap.get(key);
        if (value == null) {
            synchronized (SQLiteHelper.class) {
                value = SQLiteHelper.newEntityUpdateAdapter(entity, onConflictStrategy);
                updateAdapterMap.put(key, value);
            }
        }
        return (EntityDeleteOrUpdateAdapter<T>) value;
    }

    /**
     * 生成根据主键查询的sql语句
     *
     * @param entity    实体信息
     * @param idInCount 根据主键id in 查询参数数量（仅单主键id有效，复合主键无效）
     * @return sql语句
     */
    public static <T> String generatePKSelectSql(Entity<T> entity, int idInCount) {
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("select * from ")
                .append(entity.getTableName()).append(" ")
                .append("where ");

        SQLiteHelper.generatePKWhere(entity, idInCount, sqlBuilder);

        return sqlBuilder.toString();
    }

    /**
     * 生成select sql
     *
     * @param selectSQL 查询语句对象
     * @return sql
     */
    public static <T> String generateSelectSql(SelectSQL<T> selectSQL) {
        StringBuilder sqlBuilder = new StringBuilder();

        //opType
        sqlBuilder.append(selectSQL.opType.name()).append(" ");

        //function
        if (selectSQL.function != null) {
            sqlBuilder.append(selectSQL.function.type.name()).append("(");
            if (selectSQL.function.type == FuncType.count) {
                sqlBuilder.append("*");
            } else {
                sqlBuilder.append(selectSQL.function.property.name);
            }
            sqlBuilder.append(") ");
        } else {
            //columns
            if (selectSQL.distinct) {
                sqlBuilder.append("distinct ");
            }

            if (!selectSQL.isHasColumn()) {
                sqlBuilder.append("* ");
            } else {
                List<Property> columns = selectSQL.getColumns();
                Property property;
                for (int i = 0, size = columns.size(); i < size; i++) {
                    property = columns.get(i);

                    if (property.getFunction() != null) {
                        Object[] funcValue = property.getFunction().value;

                        if(funcValue != null && funcValue.length > 0 && (property.getFunction().type == FuncType.sum || property.getFunction().type == FuncType.avg)){
                            sqlBuilder.append(FuncType.round.name())
                                    .append("(");
                        }else{
                            sqlBuilder.append(property.getFunction().type.name())
                                    .append("(");
                        }

                        switch (property.getFunction().type) {
                            case strFTime:
                                sqlBuilder.append(String.format(
                                        "'%s', datetime(%s.%s/1000, 'unixepoch','localtime')"
                                        , property.getFunction().value[0]
                                        , property.tableName
                                        , property
                                ));
                                break;
                            case dateTime:
                            case date:
                            case time:
                                sqlBuilder.append(String.format("%s.%s/1000, 'unixepoch','localtime'", property.tableName, property));
                                break;
                            default:
                                if (funcValue != null && funcValue.length > 0) {
                                    if (property.getFunction().type == FuncType.sum || property.getFunction().type == FuncType.avg) {
                                        sqlBuilder.append(String.format("%s(%s.%s),%s"
                                                , property.getFunction().type.name()
                                                , property.tableName
                                                , property
                                                , funcValue[0]
                                        ));
                                    } else {
                                        for (int index = 0, len = funcValue.length; index < len; index++) {
                                            sqlBuilder.append(Utils.getSqlValue(funcValue[index]));
                                            if (index + 1 < len) {
                                                sqlBuilder.append(",");
                                            }
                                        }
                                    }
                                } else {
                                    sqlBuilder
                                            .append(property.tableName)
                                            .append(".")
                                            .append(property);
                                }
                        }
                    } else {
                        sqlBuilder
                                .append(property.tableName)
                                .append(".")
                                .append(property);
                    }

                    if (property.getFunction() != null) {
                        sqlBuilder.append(")");
                    }

                    sqlBuilder.append(" ");

                    if (!TextUtils.isEmpty(property.getAlias())) {
                        sqlBuilder.append(property.getAlias()).append(" ");
                    } else if (property.getFunction() != null) {
                        sqlBuilder.append(property).append(" ");
                    }

                    if (i + 1 < size) {
                        sqlBuilder.append(", ");
                    }
                }
            }
        }

        //from
        sqlBuilder.append("from ").append(selectSQL.from.getTableName()).append(" ");

        //join
        if (selectSQL.joins != null && !selectSQL.joins.isEmpty()) {
            for (Join join : selectSQL.joins) {
                sqlBuilder.append(join.type.getExp()).append(" ")
                        .append(join.targetEntity.getTableName()).append(" ");

                if (join.on != null) {
                    sqlBuilder.append("on ");
                    SQLiteHelper.generateWhereSql(join.on, sqlBuilder);
                }
            }
        }

        //where
        if (selectSQL.where != null && selectSQL.where.isValid()) {
            sqlBuilder.append("where ");
            SQLiteHelper.generateWhereSql(selectSQL.where, sqlBuilder);
        }

        //groupBy
        if (selectSQL.groupBy != null) {
            sqlBuilder.append("group by ");
            for (int i = 0, length = selectSQL.groupBy.property.length; i < length; i++) {
                Property p = selectSQL.groupBy.property[i];
                sqlBuilder.append(p.tableName).append(".").append(p.name);
                if (i + 1 < length) {
                    sqlBuilder.append(",");
                }
            }
            sqlBuilder.append(" ");
        }

        //orderBy
        if (selectSQL.orderBy != null) {
            sqlBuilder.append("order by ");
            for (int i = 0, length = selectSQL.orderBy.property.length; i < length; i++) {
                Property p = selectSQL.orderBy.property[i];
                OrderByType orderByType = selectSQL.orderBy.type[i];
                sqlBuilder.append(p.tableName).append(".").append(p.name).append(" ")
                        .append(orderByType.name());
                if (i + 1 < length) {
                    sqlBuilder.append(",");
                }
            }
        }

        //limit
        if (selectSQL.page != null) {
            sqlBuilder.append(" limit ").append(selectSQL.page.limit)
                    .append(" offset ").append(selectSQL.page.offset)
                    .append(" ");
        }

        return sqlBuilder.toString();
    }

    /**
     * 生成根据主键更新的sql语句
     *
     * @param entity 实体信息
     * @return sql语句
     */
    public static <T> String generatePKUpdateSql(Entity<T> entity, int onConflictStrategy) {
        String strategy;
        switch (onConflictStrategy) {
            case OnConflictStrategy.REPLACE:
                strategy = "or replace ";
                break;
            case OnConflictStrategy.FAIL:
            case OnConflictStrategy.ROLLBACK:
            case OnConflictStrategy.ABORT:
                strategy = "or abort ";
                break;
            case OnConflictStrategy.IGNORE:
                strategy = "or ignore ";
                break;
            case OnConflictStrategy.NONE:
            default:
                strategy = "";
        }

        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("update ")
                .append(strategy)
                .append(entity.getTableName()).append(" set ");

        for (int i = 0, size = entity.getAllProperties().size(); i < size; i++) {
            sqlBuilder.append(entity.getAllProperties().get(i)).append(" = ? ");
            if (i + 1 < size) {
                sqlBuilder.append(", ");
            }
        }

        sqlBuilder.append("where ");
        SQLiteHelper.generatePKWhere(entity, 1, sqlBuilder);

        return sqlBuilder.toString();
    }

    /**
     * 生成Update sql
     *
     * @param updateSQL update sql对象
     * @return sql
     */
    public static <T> String generateUpdateSql(UpdateSQL<T> updateSQL) {
        StringBuilder sqlBuilder = new StringBuilder();

        sqlBuilder.append(updateSQL.opType.name()).append(" ")
                .append(updateSQL.from.getTableName())
                .append(" set ");

        for (int i = 0, size = updateSQL.setFields.size(); i < size; i++) {
            sqlBuilder.append(updateSQL.setFields.get(i).property).append(" = ? ");
            if (i + 1 < size) {
                sqlBuilder.append(", ");
            }
        }

        if (updateSQL.where != null && updateSQL.where.isValid()) {
            sqlBuilder.append("where ");
            SQLiteHelper.generateWhereSql(updateSQL.where, sqlBuilder);
        }

        return sqlBuilder.toString();
    }

    /**
     * 生成根据主键删除的sql语句
     *
     * @param entity    实体信息
     * @param idInCount 根据主键id in 查询参数数量（仅单主键id有效，复合主键无效）
     * @return sql语句
     */
    public static <T> String generatePKDeleteSql(Entity<T> entity, int idInCount) {
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("delete from ")
                .append(entity.getTableName())
                .append(" where ");

        SQLiteHelper.generatePKWhere(entity, idInCount, sqlBuilder);

        return sqlBuilder.toString();
    }

    /**
     * 生成Delete sql
     *
     * @param deleteSQL delete sql对象
     * @return sql
     */
    public static <T> String generateDeleteSql(DeleteSQL<T> deleteSQL) {
        StringBuilder sqlBuilder = new StringBuilder();

        sqlBuilder.append(deleteSQL.opType.name()).append(" from ")
                .append(deleteSQL.from.getTableName()).append(" ");

        if (deleteSQL.where != null && deleteSQL.where.isValid()) {
            sqlBuilder.append("where ");
            SQLiteHelper.generateWhereSql(deleteSQL.where, sqlBuilder);
        }

        return sqlBuilder.toString();
    }

    /**
     * 生成Insert sql
     *
     * @param entity             实体信息
     * @param onConflictStrategy insert语句时有效
     * @return sql
     */
    public static <T> String generateInsertSql(Entity<T> entity, int onConflictStrategy) {
        String strategy;
        switch (onConflictStrategy) {
            case OnConflictStrategy.REPLACE:
                strategy = "or replace ";
                break;
            case OnConflictStrategy.FAIL:
            case OnConflictStrategy.ROLLBACK:
            case OnConflictStrategy.ABORT:
                strategy = "or abort ";
                break;
            case OnConflictStrategy.IGNORE:
                strategy = "or ignore ";
                break;
            case OnConflictStrategy.NONE:
            default:
                strategy = "";
        }

        StringBuilder sqlBuilder = new StringBuilder();
        StringBuilder valuesBuilder = new StringBuilder();

        sqlBuilder.append("insert ")
                .append(strategy)
                .append("into `")
                .append(entity.getTableName()).append("` ")
                .append("(");

        Property p;
        for (int i = 0, size = entity.getAllProperties().size(); i < size; i++) {
            p = entity.getAllProperties().get(i);

            sqlBuilder.append("`").append(p.name).append("`");

            if (p.isId) {
                valuesBuilder.append("nullif(?,0)");
            } else {
                valuesBuilder.append("?");
            }

            if (i + 1 < size) {
                sqlBuilder.append(",");
                valuesBuilder.append(",");
            }
        }

        sqlBuilder.append(") ")
                .append("values (")
                .append(valuesBuilder)
                .append(")");

        return sqlBuilder.toString();
    }

    /**
     * 绑定sql语句属性值
     *
     * @param stmt        sql语句对象
     * @param expressions 绑定条件值
     */
    public static void bindPropertyValue(SQLiteStatement stmt, List<Expression> expressions) {
        if (expressions == null || expressions.isEmpty()) {
            return;
        }

        int bindIndex = 0;
        for (Expression item : expressions) {
            switch (item.expressionType) {
                case between:
                    if (item.value.length != 2) {
                        throw new IllegalArgumentException("'between...and...' The number of bound values does not match.");
                    }
                    SQLiteHelper.bindPropertyValue(item.property, stmt, ++bindIndex, item.value[0]);
                    SQLiteHelper.bindPropertyValue(item.property, stmt, ++bindIndex, item.value[1]);
                    break;
                case equal:
                case notEqual:
                case less:
                case lessOrEqual:
                case greater:
                case greaterOrEqual:
                case like:
                case contains:
                case startWith:
                case endWith:
                case regExp:
                    SQLiteHelper.bindPropertyValue(item.property, stmt, ++bindIndex, item.value[0]);
                    break;
                case in:
                    for (Object value : item.value) {
                        SQLiteHelper.bindPropertyValue(item.property, stmt, ++bindIndex, value);
                    }
                    break;
                case isNull:
                case notNull:
                    break;
            }
        }
    }

    /**
     * 绑定sql语句属性值
     *
     * @param property  属性对象
     * @param stmt      sql语句对象
     * @param bindIndex 绑定位置(从1开始)
     * @param value     绑定值
     */
    public static void bindPropertyValue(Property property, SQLiteStatement stmt, int bindIndex, Object value) {
        if (property == null) {
            return;
        }
        SQLiteHelper.bindStmtValue(property.type.getName(), stmt, bindIndex, value);
    }

    /**
     * 绑定sql语句参数
     *
     * @param typeName  绑定参数类型名称
     * @param stmt      sql语句对象
     * @param bindIndex 绑定位置(从1开始)
     * @param value     绑定值
     */
    private static void bindStmtValue(String typeName, SQLiteStatement stmt, int bindIndex, Object value) {
        if (value == null) {
            stmt.bindNull(bindIndex);
            return;
        }

        switch (Utils.getOrDefault(typeName, "")) {
            case "java.lang.Boolean":
            case "boolean":
                if (Utils.isNum(value)) {
                    stmt.bindLong(bindIndex, ((long) Double.parseDouble(value.toString())) == 0 ? 0 : 1);
                } else {
                    stmt.bindLong(bindIndex, Boolean.parseBoolean(value.toString()) ? 1 : 0);
                }
                break;
            case "java.lang.Integer":
            case "int":
            case "java.lang.Long":
            case "long":
            case "java.lang.Short":
            case "short":
                if (Utils.isNum(value.toString())) {
                    stmt.bindLong(bindIndex, (long) Double.parseDouble(value.toString()));
                } else {
                    stmt.bindText(bindIndex, value.toString());
                }
                break;
            case "java.lang.Byte":
            case "byte":
                if (value.getClass().isAssignableFrom(byte.class) || value.getClass().isAssignableFrom(Byte.class)) {
                    stmt.bindBlob(bindIndex, new byte[]{(byte) value});
                } else if (value.getClass().isAssignableFrom(byte[].class) || value.getClass().isAssignableFrom(Byte[].class)) {
                    stmt.bindBlob(bindIndex, (byte[]) value);
                } else {
                    stmt.bindText(bindIndex, value.toString());
                }
                break;
            case "java.lang.Float":
            case "float":
            case "java.lang.Double":
            case "double":
                if (Utils.isNum(value.toString())) {
                    stmt.bindDouble(bindIndex, Double.parseDouble(value.toString()));
                } else {
                    stmt.bindText(bindIndex, value.toString());
                }
                break;
            case "java.util.Date":
                if (value.getClass().isAssignableFrom(Date.class)) {
                    stmt.bindLong(bindIndex, ((Date) value).getTime());
                } else if (Utils.isNum(value)) {
                    stmt.bindLong(bindIndex, (long) Double.parseDouble(value.toString()));
                } else {
                    stmt.bindText(bindIndex, value.toString());
                }
                break;
            default:
                stmt.bindText(bindIndex, value.toString());
        }
    }

    /**
     * 执行删除sql语句
     *
     * @param db               RoomDatabase
     * @param deleteSQL        删除语句对象
     * @param isReturnRowCount 是否返回影响行数
     * @return 影响行数
     */
    public static int execDeleteSql(RoomDatabase db, DeleteSQL<?> deleteSQL, boolean isReturnRowCount) {
        final String sql = SQLiteHelper.generateDeleteSql(deleteSQL);
        final List<Expression> argumentList = deleteSQL.getArgumentList();

        if (RoomOperator.PRINT_SQL_LOG) {
            logSql(sql, argumentList);
        }

        return DBUtil.performBlocking(db, false, true, connection -> {
            try (SQLiteStatement stmt = connection.prepare(sql)) {
                SQLiteHelper.bindPropertyValue(stmt, argumentList);
                stmt.step();
                return isReturnRowCount ? SQLiteConnectionUtil.getTotalChangedRows(connection) : -1;
            }
        });
    }

    /**
     * 执行更新sql语句
     *
     * @param db               RoomDatabase
     * @param updateSQL        更新语句对象
     * @param isReturnRowCount 是否返回影响行数
     * @return 影响行数
     */
    public static <T> int execUpdateSql(RoomDatabase db, UpdateSQL<?> updateSQL, boolean isReturnRowCount) {
        final String sql = updateSQL.getSql();
        final List<Expression> argumentList = updateSQL.getArgumentList();

        if (RoomOperator.PRINT_SQL_LOG) {
            logSql(sql, argumentList);
        }

        return DBUtil.performBlocking(db, false, true, connection -> {
            try (SQLiteStatement stmt = connection.prepare(sql)) {
                SQLiteHelper.bindPropertyValue(stmt, argumentList);
                stmt.step();
                return isReturnRowCount ? SQLiteConnectionUtil.getTotalChangedRows(connection) : -1;
            }
        });
    }

    /**
     * 执行查询sql语句
     *
     * @param db        RoomDatabase
     * @param selectSQL 查询语句对象
     * @return 结果集
     */
    public static <T> List<T> execQuerySql(RoomDatabase db, SelectSQL<?> selectSQL) {
        return SQLiteHelper.execQuerySql(db, selectSQL, false);
    }

    /**
     * 执行查询sql语句
     *
     * @param db        RoomDatabase
     * @param selectSQL 查询语句对象
     * @return 结果
     */
    public static <T> T execQuerySqlReturnOne(RoomDatabase db, SelectSQL<?> selectSQL) {
        List<T> data = SQLiteHelper.execQuerySql(db, selectSQL, true);

        if (data == null || data.isEmpty()) {
            return null;
        }
        return data.get(0);
    }

    /**
     * 执行查询sql语句
     *
     * @param db          RoomDatabase
     * @param selectSQL   查询语句对象
     * @param isResultOne 是否只获取一条数据
     * @return 结果集
     */
    private static <T> List<T> execQuerySql(RoomDatabase db, SelectSQL<?> selectSQL, boolean isResultOne) {
        final String sql = selectSQL.getSql();
        final List<Expression> argumentList = selectSQL.getArgumentList();

        if (RoomOperator.PRINT_SQL_LOG) {
            logSql(sql, argumentList);
        }

        return DBUtil.performBlocking(db, true, false, connection -> {
            try (SQLiteStatement statement = connection.prepare(sql)) {
                //绑定sql where 条件值
                if (selectSQL.where != null) {
                    if (argumentList != null && !argumentList.isEmpty()) {
                        SQLiteHelper.bindPropertyValue(statement, argumentList);
                    }
                }

                //绑定sql where 条件值
                if (selectSQL.where != null) {
                    if (argumentList != null && !argumentList.isEmpty()) {
                        SQLiteHelper.bindPropertyValue(statement, argumentList);
                    }
                }

                //查询列
                final List<Property> selectColumns = selectSQL.getColumns();

                //是否属于指定获取单列
                final boolean isSingleColumn = selectSQL.isSingleColumn();

                ArrayMap<String, Integer> columnIndexMap = null;

                if (selectSQL.function == null) {
                    columnIndexMap = new ArrayMap<>();
                    String field;
                    for (Property item : selectColumns) {
                        field = Utils.getStrDefault(item.getAlias(), item.name);
                        columnIndexMap.put(field, SQLiteStatementUtil.getColumnIndexOrThrow(statement, field));
                    }
                }

                final List<Object> result = new ArrayList<>();
                ArrayMap<String, Object> itemMap = null;
                Object value;
                String resultMapKey;
                boolean isModelObj = false;//是否属于实体对象
                boolean isListJson = false;//false:json列表、true:实体对象json

                while (statement.step()) {
                    if (!isSingleColumn) {
                        itemMap = new ArrayMap<>();
                    }

                    if (selectSQL.function != null) {
                        result.add(statement.getDouble(0));
                    } else {
                        for (Property p : selectColumns) {
                            Integer index = columnIndexMap != null ? columnIndexMap.get(Utils.getStrDefault(p.getAlias(), p.name)) : null;
                            if (index == null) {
                                continue;
                            }

                            //获取map结果集key
                            resultMapKey = Utils.getStrDefault(p.getAlias(), p.javaName);

                            if (statement.isNull(index)) {
                                if (!isSingleColumn) {
                                    itemMap.put(resultMapKey, null);
                                }
                                continue;
                            }

                            isModelObj = false;
                            isListJson = false;
                            switch (p.type.getName()) {
                                case "java.lang.Boolean":
                                case "boolean":
                                case "java.lang.Integer":
                                case "int":
                                case "java.lang.Short":
                                case "short":
                                    value = statement.getInt(index);
                                    break;
                                case "java.lang.Long":
                                case "long":
                                case "java.util.Date":
                                    value = statement.getLong(index);
                                    break;
                                case "java.lang.Byte":
                                case "byte":
                                    value = statement.getBlob(index);
                                    break;
                                case "java.lang.Float":
                                case "float":
                                    value = statement.getFloat(index);
                                    break;
                                case "java.lang.Double":
                                case "double":
                                    value = statement.getDouble(index);
                                    break;
                                case "java.lang.String":
                                    value = statement.getText(index);
                                    break;
                                default:
                                    isModelObj = true;
                                    String res = statement.getText(index);
                                    value = res;
                                    isListJson = Utils.isListJson(res);
                            }

                            if (isSingleColumn) {
                                if (isModelObj) {
                                    result.add(isListJson ? Utils.toJSONArray(value) : Utils.toJSONObject(value));
                                } else {
                                    result.add(value);
                                }
                            } else {
                                if (isModelObj) {
                                    itemMap.put(resultMapKey, isListJson ? Utils.toJSONArray(value) : Utils.toJSONObject(value));
                                } else {
                                    itemMap.put(resultMapKey, value);
                                }
                            }
                        }
                    }

                    if (!isSingleColumn) {
                        result.add(itemMap);
                    }

                    if (isResultOne) {
                        break;
                    }
                }

                return (List<T>) JSON.parseArray(JSON.toJSONString(result), TypeUtils.getClass(selectSQL.getResultType()));
            }
        });
    }

    /**
     * 执行查询sql语句
     *
     * @param db          RoomDatabase
     * @param sql         查询语句对象
     * @param isResultOne 是否只获取一条数据
     * @param resultType  返回数据泛型类型
     * @return 结果集
     */
    public static <T> List<T> execQuerySql(RoomDatabase db, String sql, Object[] arguments, boolean isResultOne, Class<T> resultType) {
        if (RoomOperator.PRINT_SQL_LOG) {
            logSql(sql, null);
        }

        return DBUtil.performBlocking(db, true, false, connection -> {
            List<Object> result = new ArrayList<>();
            try (SQLiteStatement statement = connection.prepare(sql)) {
                //绑定sql参数
                if (arguments != null && arguments.length > 0) {
                    int bindIndex = 0;
                    for (Object item : arguments) {
                        SQLiteHelper.bindStmtValue(item != null ? item.getClass().getName() : null, statement, ++bindIndex, item);
                    }
                }

                int columnCount;
                String columnName;
                Object value = null;
                ArrayMap<String, Object> itemMap;

                while (statement.step()) {
                    columnCount = statement.getColumnCount();
                    itemMap = new ArrayMap<>();
                    value = null;

                    for (int i = 0; i < columnCount; i++) {
                        columnName = statement.getColumnName(i);
                        value = switch (statement.getColumnType(i)) {
                            case SQLite.SQLITE_DATA_NULL -> null;
                            case SQLite.SQLITE_DATA_INTEGER -> statement.getLong(i);
                            case SQLite.SQLITE_DATA_FLOAT -> statement.getDouble(i);
                            case SQLite.SQLITE_DATA_TEXT -> statement.getText(i);
                            case SQLite.SQLITE_DATA_BLOB -> statement.getBlob(i);
                            default -> value;
                        };

                        if (isResultOne) {
                            break;
                        } else {
                            itemMap.put(columnName, value);
                        }
                    }

                    if (isResultOne) {
                        result.add(value);
                        break;
                    } else {
                        result.add(itemMap);
                    }
                }

                return JSON.parseArray(JSON.toJSONString(result), resultType);
            }
        });
    }

    /**
     * 生成where 子句
     *
     * @param condition  条件列表
     * @param sqlBuilder sql构造器
     */
    private static void generateWhereSql(Condition condition, StringBuilder sqlBuilder) {
        if (condition == null) {
            return;
        }

        if (!condition.isConditionEmpty()) {
            StringBuilder leftValueBuilder = new StringBuilder();
            for (int j = 0; j < condition.expressions.size(); j++) {
                Expression expression = condition.expressions.get(j);
                leftValueBuilder.setLength(0);

                if (j > 0) {
                    sqlBuilder.append(condition.whereRelation().name()).append(" ");
                }

                if (expression.isNot) {
                    sqlBuilder.append("not").append(" ");
                }

                if (expression.leftChildSelectSQL != null) {
                    leftValueBuilder.append("(").append(expression.leftChildSelectSQL.getSql()).append(") ");
                } else if (expression.property != null) {
                    leftValueBuilder
                            .append(expression.property.tableName)
                            .append(".")
                            .append(expression.property)
                            .append(" ");
                }

                if (expression.isValueProperty) {
                    SQLiteHelper.handleValuePropertyCondition(expression, leftValueBuilder, sqlBuilder);
                    continue;
                }

                switch (expression.expressionType) {
                    case equal:
                    case notEqual:
                    case less:
                    case lessOrEqual:
                    case greater:
                    case greaterOrEqual:
                    case like:
                    case regExp:
                    case contains:
                    case startWith:
                    case endWith:
                        if (expression.leftChildSelectSQL != null && expression.rightChildSelectSQL != null) {
                            if (expression.isReverse && expression.expressionType.getReverseFormat() != null) {
                                String format = expression.expressionType.getReverseFormat().replace("%s", "(%s)").replace("?", "(%s)");
                                sqlBuilder.append(String.format(format, expression.rightChildSelectSQL, expression.leftChildSelectSQL))
                                        .append(" ");
                            } else {
                                String format = expression.expressionType.getFormat().replace("%s", "(%s)").replace("?", "(%s)");
                                format = String.format("(%s) %s", expression.leftChildSelectSQL, format);
                                sqlBuilder.append(String.format(format, expression.rightChildSelectSQL))
                                        .append(" ");
                            }
                        } else if (expression.rightChildSelectSQL != null) {
                            if (expression.isReverse && expression.expressionType.getReverseFormat() != null) {
                                String format = expression.expressionType.getReverseFormat().replace("?", "(%s)");
                                sqlBuilder.append(String.format(format, expression.rightChildSelectSQL, leftValueBuilder))
                                        .append(" ");
                            } else {
                                sqlBuilder.append(leftValueBuilder)
                                        .append(String.format(expression.expressionType.getFormat(), String.format("(%s) ", expression.rightChildSelectSQL.getSql())))
                                        .append(" ");
                            }
                        } else {
                            if (expression.isReverse && expression.expressionType.getReverseFormat() != null) {
                                sqlBuilder.append(String.format(expression.expressionType.getReverseFormat(), leftValueBuilder))
                                        .append(" ");
                            } else {
                                sqlBuilder.append(leftValueBuilder)
                                        .append(expression.expressionType.getExp())
                                        .append(" ");
                            }
                        }
                        break;
                    case between:
                    case isNull:
                    case notNull:
                        sqlBuilder.append(leftValueBuilder);
                        sqlBuilder.append(expression.expressionType.getExp())
                                .append(" ");
                        break;
                    case in:
                        sqlBuilder.append(leftValueBuilder);
                        if (expression.rightChildSelectSQL != null) {
                            sqlBuilder.append(String.format(expression.expressionType.getFormat()
                                            , expression.rightChildSelectSQL.getSql()))
                                    .append(" ");
                        } else {
                            sqlBuilder.append(expression.expressionType.getExp())
                                    .append("(");
                            StringUtil.appendPlaceholders(sqlBuilder, expression.value.length);
                            sqlBuilder.append(") ");
                        }
                        break;
                    case exists:
                        sqlBuilder.append(leftValueBuilder);
                        if (expression.rightChildSelectSQL != null) {
                            sqlBuilder.append(expression.expressionType.getExp())
                                    .append(" ")
                                    .append("(")
                                    .append(expression.rightChildSelectSQL.getSql())
                                    .append(") ")
                                    .append(" ");
                        }
                }
            }
        }

        if (!condition.isWhereEmpty()) {
            Condition childCondition;
            for (int i = 0; i < condition.conditionList.size(); i++) {
                childCondition = condition.conditionList.get(i);
                if (i > 0 || !condition.isConditionEmpty()) {
                    sqlBuilder.append(" ").append(childCondition.whereRelation().name());
                }

                if (!childCondition.isWhereEmpty() && !childCondition.isConditionEmpty()) {
                    if (childCondition.isNot) {
                        sqlBuilder.append(" not ( ");
                    } else {
                        sqlBuilder.append(" ( ");
                    }
                } else {
                    if (childCondition.isNot) {
                        sqlBuilder.append(" not ");
                    } else {
                        sqlBuilder.append(" ");
                    }
                }

                SQLiteHelper.generateWhereSql(childCondition, sqlBuilder);

                if (!childCondition.isWhereEmpty() && !childCondition.isConditionEmpty()) {
                    sqlBuilder.append(" ) ");
                }
            }
        }
    }

    /**
     * 处理条件中value属于Property对象的条件
     *
     * @param expression 条件
     * @param sqlBuilder sqlBuilder
     */
    private static void handleValuePropertyCondition(Expression expression, StringBuilder leftValueBuilder, StringBuilder sqlBuilder) {
        Property value;
        switch (expression.expressionType) {
            case equal:
            case notEqual:
            case less:
            case lessOrEqual:
            case greater:
            case greaterOrEqual:
            case like:
            case regExp:
            case contains:
            case startWith:
            case endWith:
                value = (Property) expression.value[0];
                if (expression.isReverse && expression.expressionType.getReverseFormat() != null) {
                    String format = expression.expressionType.getReverseFormat().replace("?", "%s");
                    sqlBuilder.append(String.format(format, String.format("%s.%s", value.tableName, value.name), leftValueBuilder))
                            .append(" ");
                } else {
                    sqlBuilder.append(leftValueBuilder)
                            .append(" ")
                            .append(String.format(expression.expressionType.getFormat(), String.format("%s.%s", value.tableName, value.name)))
                            .append(" ");
                }
                break;
            case between:
                value = (Property) expression.value[0];
                Property value2 = (Property) expression.value[1];
                sqlBuilder.append(leftValueBuilder)
                        .append(" ")
                        .append(String.format(
                                expression.expressionType.getFormat()
                                , String.format("%s.%s", value.tableName, value.name)
                                , String.format("%s.%s", value2.tableName, value2.name)
                        ))
                        .append(" ");
                break;
            case isNull:
            case notNull:
                //无需处理
                break;
            case in:
                StringBuilder content = new StringBuilder();
                for (Object item : expression.value) {
                    if (item instanceof Property) {
                        Property p = (Property) item;
                        content.append(p.tableName).append(".").append(p.name).append(", ");
                        continue;
                    }

                    switch (expression.property.type.getName()) {
                        case "java.lang.Boolean":
                        case "boolean":
                            if (item instanceof Long) {
                                content.append(Long.parseLong(item.toString()) == 0 ? 0 : 1);
                            } else if (item instanceof String) {
                                content.append("true".equalsIgnoreCase(item.toString()) || !"0".equals(item.toString()) ? 1 : 0);
                            } else {
                                content.append(Boolean.parseBoolean(item.toString()) ? 1 : 0);
                            }
                            content.append(",");
                            break;
                        case "java.lang.Integer":
                        case "int":
                        case "java.lang.Long":
                        case "long":
                        case "java.lang.Short":
                        case "short":
                        /*case "java.lang.Byte":
                        case "byte":*/
                        case "java.lang.Float":
                        case "float":
                        case "java.lang.Double":
                        case "double":
                            content.append(item).append(",");
                            break;
                        case "java.util.Date":
                            if (item instanceof Date) {
                                content.append(((Date) item).getTime()).append(",");
                            } else {
                                content.append(Long.parseLong(item.toString())).append(",");
                            }
                            break;
                        default:
                            content.append("'").append(item).append("'").append(",");
                    }
                }

                String result = null;
                if (content.length() > 0) {
                    result = content.substring(0, content.length() - 1);
                }

                sqlBuilder.append(leftValueBuilder)
                        .append(" ")
                        .append(String.format(expression.expressionType.getFormat(), result))
                        .append(" ");

                break;
        }
    }


    /**
     * 生成根据主键查询的where条件sql语句
     *
     * @param entity     实体信息
     * @param idInCount  根据主键id in 查询参数数量（仅单主键id有效，复合主键无效）
     * @param sqlBuilder sql构造
     */
    private static <T> void generatePKWhere(Entity<T> entity, int idInCount, StringBuilder sqlBuilder) {
        if (entity.getIdProperty() != null) {
            sqlBuilder.append(entity.getIdProperty());
            if (idInCount > 1) {
                sqlBuilder.append(" in(");
                StringUtil.appendPlaceholders(sqlBuilder, idInCount);
                sqlBuilder.append(") ");
            } else {
                sqlBuilder.append(" = ? ");
            }
            return;
        }

        if (entity.getCompositePK() != null) {
            for (int i = 0, size = entity.getCompositePK().size(); i < size; i++) {
                if (i >= 1) {
                    sqlBuilder.append(" ").append("and");
                }
                sqlBuilder.append(" ").append(entity.getCompositePK().get(i)).append(" = ? ");
            }
        }
    }

    /**
     * 生成获取EntityInsertionAdapter
     *
     * @param entity             实体信息
     * @param onConflictStrategy insert 策略(详见OnConflictStrategy)
     */
    private static <T> EntityInsertAdapter<T> newEntityInsertionAdapter(Entity<T> entity, int onConflictStrategy) {
        final String sql = SQLiteHelper.generateInsertSql(entity, onConflictStrategy);
        return new EntityInsertAdapter<T>() {
            @NonNull
            @Override
            public String createQuery() {
                return sql;
            }

            @Override
            public void bind(@NonNull SQLiteStatement stmt, T data) {
                entity.bindInsertionAdapterValue(stmt, data);
            }
        };
    }

    /**
     * 生成获取delete adapter
     *
     * @param entity 实体信息
     */
    private static <T> EntityDeleteOrUpdateAdapter<T> newEntityDeleteAdapter(Entity<T> entity) {
        final String sql = SQLiteHelper.generatePKDeleteSql(entity, 1);
        return new EntityDeleteOrUpdateAdapter<T>() {
            @NonNull
            @Override
            public String createQuery() {
                return sql;
            }

            @Override
            public void bind(@NonNull SQLiteStatement stmt, T value) {
                entity.bindDeleteAdapterValue(stmt, value);
            }
        };
    }

    /**
     * 生成获取update adapter
     *
     * @param entity             实体信息
     * @param onConflictStrategy insert 策略(详见OnConflictStrategy)
     */
    private static <T> EntityDeleteOrUpdateAdapter<T> newEntityUpdateAdapter(Entity<T> entity, int onConflictStrategy) {
        final String sql = SQLiteHelper.generatePKUpdateSql(entity, onConflictStrategy);
        return new EntityDeleteOrUpdateAdapter<T>() {
            @NonNull
            @Override
            public String createQuery() {
                return sql;
            }

            @Override
            public void bind(@NonNull SQLiteStatement stmt, T value) {
                entity.bindUpdateAdapterValue(stmt, value);
            }
        };
    }

    /**
     * 打印sql日志
     *
     * @param sql            原始sql语句
     * @param expressionList sql 参数列表
     */
    private static void logSql(final String sql, List<Expression> expressionList) {
        Log.d(TAG, "原始SQL: " + sql);

        if (expressionList == null || expressionList.isEmpty()) {
            return;
        }

        String logSql = sql;
        for (Expression item : expressionList) {
            if (item.value == null) {
                continue;
            }
            for (Object value : item.value) {
                if (item.property == null) {
                    logSql = logSql.replaceFirst("\\?", value == null ? "null" : Utils.getSqlValue(value).toString());
                    continue;
                }

                if (value == null) {
                    logSql = logSql.replaceFirst("\\?", "null");
                    continue;
                }

                switch (item.property.type.getName()) {
                    case "java.lang.Boolean":
                    case "boolean":
                        if (Utils.isNum(value)) {
                            logSql = logSql.replaceFirst("\\?", ((long) Double.parseDouble(value.toString())) == 0 ? "0" : "1");
                        } else {
                            logSql = logSql.replaceFirst("\\?", Boolean.parseBoolean(value.toString()) ? "1" : "0");
                        }
                        break;
                    case "java.lang.Integer":
                    case "int":
                    case "java.lang.Long":
                    case "long":
                    case "java.lang.Short":
                    case "short":
                    case "java.lang.Byte":
                    case "byte":
                    case "java.lang.Float":
                    case "float":
                    case "java.lang.Double":
                    case "double":
                        if (Utils.isNum(value)) {
                            logSql = logSql.replaceFirst("\\?", value.toString());
                        } else {
                            logSql = logSql.replaceFirst("\\?", String.format("'%s'", value));
                        }
                        break;
                    case "java.util.Date":
                        if (value.getClass().isAssignableFrom(Date.class)) {
                            logSql = logSql.replaceFirst("\\?", String.valueOf(((Date) value).getTime()));
                        } else if (Utils.isNum(value)) {
                            logSql = logSql.replaceFirst("\\?", String.valueOf((long) Double.parseDouble(value.toString())));
                        } else {
                            logSql = logSql.replaceFirst("\\?", String.format("'%s'", value));
                        }
                        break;
                    default:
                        logSql = logSql.replaceFirst("\\?", String.format("'%s'", value));
                }

            }
        }

        Log.d(TAG, "日志SQL: " + logSql);
    }
}
